﻿@using System.Configuration
@using System.Data.SqlClient
@{
    if ((Request.QueryString.Count > 0) && (Request.QueryString["RPID"] != null) && (Request.QueryString["MM"] != null) && (Request.QueryString["TYPE"] != null))
    {
        
        string reportingPeriodId = Request.QueryString["RPID"].Trim();
        string mm = Request.QueryString["MM"].Trim();
        string claimType = Request.QueryString["TYPE"].Trim().ToUpperInvariant();
        
        switch (claimType)
        {
            case "CUR":
                claimType = "CURRENT";
                break;
            case "ARR":
                claimType = "ARREAR";
                break;
        }
        
        DateTime mmDateTime = DateTime.ParseExact(mm, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

        string fileName = string.Format("Export_To_SAP_RP{0}_{1}.csv", reportingPeriodId, mmDateTime.ToString("MMM"));

        System.Data.DataSet dsDownloadClaims = null;
        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
        {
            System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
            dsDownloadClaims = new System.Data.DataSet("Claims_DataSet");


            cmd.CommandText = "SELECT 'Export_To_SAP_' + CONVERT(VARCHAR,YEAR(EndDate)) + SUBSTRING(UPPER(DATENAME([MONTH], EndDate)), 1, 3) AS 'FileName', * FROM ReportingPeriod WHERE Id = @RPID;";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@RPID", reportingPeriodId);
            da.Fill(dsDownloadClaims, "ReportingPeriod");
            if (dsDownloadClaims.Tables["ReportingPeriod"].Rows.Count > 0)
            {
                // Export_To_SAP_YYYYMMM_CURRENT_<YYYYMMDD_HHMM>.csv
                fileName = string.Format("{0}_{1}_{2}.csv", 
                    dsDownloadClaims.Tables["ReportingPeriod"].Rows[0]["FileName"].ToString(), 
                    claimType,
                    DateTime.Now.ToString("yyyyMMdd_HHmm"));
            }

            cmd.CommandText = @"
SELECT  B2E.NameLegal,
        a.EntryOwner,
        a.OT_MONTH,
        SUM(a.OT1_0) AS 'OT1_0',
        SUM(a.OT1_5) AS 'OT1_5',
        SUM(a.OT2_0) AS 'OT2_0',
        SUM(a.AlwShft) AS 'AlwShft',
        SUM(a.AlwMeal) AS 'AlwMeal'
FROM 
(
    SELECT  rpc.EntryOwner, 
            SUBSTRING(CONVERT(VARCHAR, rpc.ClaimPeriod, 111), 1, 7) AS 'OT_MONTH',
            0 AS 'OT1_0',
            0 AS 'OT1_5',
            0 AS 'OT2_0',
            0 AS 'AlwShft',
            0 AS 'AlwMeal'
    FROM    ReportingPeriodClaimant rpc
    WHERE   rpc.ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1)
            AND rpc.ClaimPeriod = @SelectedMonth
    UNION 
    SELECT  c.EntryOwner, 
            SUBSTRING(CONVERT(VARCHAR, c.ClaimDate, 111), 1, 7) AS 'OT_MONTH',
            CONVERT(DECIMAL(6,2),
            (ISNULL(SUM(CONVERT(INT, c.PH)), 0) * 8) +
            (ISNULL(SUM(CONVERT(INT, c.RD4)), 0) * 8) +
            (ISNULL(SUM(CONVERT(INT, c.RD8)), 0) * 16)) AS 'OT1_0',
            ISNULL(SUM(c.OT1_5), 0) AS 'OT1_5',
            ISNULL(SUM(c.OT2_0), 0) AS 'OT2_0',
            ISNULL(SUM(c.AlwShft), 0) AS 'AlwShft',
            ISNULL(SUM(c.AlwMeal), 0) AS 'AlwMeal'
    FROM    ReportingPeriodClaimant rpc
    LEFT OUTER JOIN 
            Claim c
            ON rpc.EntryOwner = c.EntryOwner
    WHERE   rpc.ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1)
            AND c.ClaimDate >= @SelectedMonth AND c.ClaimDate < DATEADD(MONTH, 1, @SelectedMonth) 
            AND c.ApproveDate < (SELECT EndDate FROM ReportingPeriod WHERE Locked = 1)
            AND rpc.ClaimPeriod = @SelectedMonth
            AND c.Status = 6
    GROUP BY c.EntryOwner, c.Status, SUBSTRING(CONVERT(VARCHAR, c.ClaimDate, 111), 1, 7)

) a
LEFT JOIN B2E
ON a.EntryOwner = B2E.EmployeeMaster
GROUP BY a.EntryOwner, B2E.NameLegal, a.OT_MONTH
ORDER BY B2E.NameLegal
";
            
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SelectedMonth", mmDateTime.ToString("yyyy-MM-dd"));

            da.Fill(dsDownloadClaims, "Claims");
            System.Data.DataTable dt = dsDownloadClaims.Tables["Claims"];
            
            if (dt.Rows.Count > 0)
            {
                int rowCount = 0;

                // Write header
                sb.AppendLine("Name\tB2E_ID\tOT_MONTH\tOT1_0\tOT1_5\tOT2_0\tAlwShft\tAlwMeal");

                // Write body
                foreach (System.Data.DataRow row in dt.Rows)
                {
                    rowCount++;

                    foreach (System.Data.DataColumn column in dt.Columns)
                    {
                        sb.Append(row[column].ToString());
                        sb.Append('\t');
                    }
                    sb.Remove(sb.Length - 1, 1);

                    if (rowCount != dt.Rows.Count)
                    {
                        sb.AppendLine();
                    }
                }
            } // end if (dt.Rows.Count > 0)
            
        }

        using (MemoryStream ms = new MemoryStream())
        {
            using (System.IO.StreamWriter sw = new StreamWriter(ms, System.Text.Encoding.Unicode))
            {
                sw.AutoFlush = true;
                sw.Write(sb.ToString());
                sw.Close();
            }

            Response.Clear();
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", fileName));
            Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
            Response.BinaryWrite(ms.ToArray());
            Response.End();
        }
    }
}